//
//  ViewController.swift
//  FMDB使用
//
//  Created by iOS on 16/10/7.
//  Copyright © 2016年 iOS. All rights reserved.
//

import UIKit

class ViewController: UIViewController {

    override func viewDidLoad() {
        super.viewDidLoad()
//        testInsert()
//        testManyInsert()
//        testUpdate()
//        testDelete()
        testQuery()
    }
    
    /// 查询数据
    private func testQuery() {
        // sql
        let sql = "SELECT * FROM T_Person WHERE id < ?;"
        
        SQLiteManager.shared.dbQueue.inDatabase { (db) in
            do {
                // 查询的结果集,并不是一次性把所有查询到的数据都放在结果集中,一次指向一条记录
                let resultSet = try db!.executeQuery(sql, values: [50])
                
                while resultSet.next() {
                    // 获取到一条记录
                    let id = resultSet.int(forColumn: "id")
                    let name = resultSet.string(forColumn: "name")!
                    let age = resultSet.int(forColumnIndex: 2)
                    let height = resultSet.double(forColumn: "height")
                    print("id = \(id), name = \(name), age = \(age), height = \(height)")
                }
            } catch let error as NSError {
                print("查询失败: \(error)")
            }
        }
    }
    
    /// 删除数据
    private func testDelete() {
        // sql
        let sql = "DELETE FROM T_Person WHERE id < 10;"
        
        // 执行sql
        SQLiteManager.shared.dbQueue.inDatabase { (db) in
            do {
                try db?.executeUpdate(sql, values: [])
                print("删除数据成功")
            } catch let error as NSError {
                print("删除数据失败: \(error)")
            }
        }
    }
    
    /// 修改数据
    private func testUpdate() {
        // sql
        let sql = "UPDATE T_Person SET name = '凤姐' WHERE id = 5;"
        
        // 执行sql
        SQLiteManager.shared.dbQueue.inDatabase { (db) in
            do {
                try db?.executeUpdate(sql, values: [])
                print("更新数据成功")
            } catch let error as NSError {
                print("更新数据失败: \(error)")
            }
        }
    }
    
    /// 批量添加数据
    private func testManyInsert() {
        // UnsafeMutablePointer<ObjCBool>?: 停止使用
        SQLiteManager.shared.dbQueue.inTransaction { (db, rollback) in
            // 停止,和OC的 *stop = YES;
//            rollback?.pointee = true
            // ? 占位
            let start = CACurrentMediaTime()
            let sql = "INSERT INTO T_Person (name, age, height) VALUES (?, ?, ?);"
            
            do {
                for i in 0..<10000 {
                    try db?.executeUpdate(sql, values: ["李四\(i)", 22, 1.66])
                    
                    if i == 1000 {
                        print("批量添加失败了")
                        rollback?.pointee = true
                        break
                    }
                }
            } catch let error as NSError {
                print("批量添加数据失败了: \(error)")
            }
            
            let end = CACurrentMediaTime()
            print("耗时: \(end - start)")
        }
    }
    
    /// 添加数据
    private func testInsert() {
        // sql
        let sql = "INSERT INTO T_Person (name, age, height) VALUES ('张三', 22, 1.71);"
        
        // 执行sql
        SQLiteManager.shared.dbQueue.inDatabase { (db) in
            do {
                try db?.executeUpdate(sql, values: [])
                print("添加数据成功")
            } catch let error as NSError {
                print("添加数据失败: \(error)")
            }
        }
    }
}

